﻿BEGIN TRY

	/*
	Este es un esquema que marca las dependencias entre tablas, 
	para saber que tablas importar primero.

	Calendars pend ???
	Banks done
	Provinces done
		Districts done
			Localities done
				Cheques done (Bank)
				MaritalStatuses done
				DocumentTypes done
				IVAs done
				Producers pend
				RiskTypes done
					Risks done
					Collectors pend
					Periods done
					Companies done
					Clients done
					Brands done
						ProducerCodes done
						CreditCards done
						CompanyRisk done
						LedgerAccounts pend
							ClientCreditCards done
							CompanyCreditCard done
							CashAccounts pend
							Addresses done
							Policies pend
							CarModels done
							CasualtyTypes done
							EndorseTypes done
								Fees pend
								Endorses pend
								Casualties pend
								AccessoryTypes pend
								CarPolicies pend
									Uses pend
									Accessories pend
									
************************ DROP TEMPORAL TABLES ******************************************	
	 
	declare @sql nvarchar(max)
	select @sql = isnull(@sql+';', '') + 'drop table ' + quotename(name)
	from tempdb..sysobjects
	where name like '#%'
	exec (@sql)

******************************************************************	

	select * from tempdb..sysobjects where name like '#%'
	
**************************************************************************************	
	*/

	BEGIN TRAN INSERT_DATA
	
	
	
	-- Calendars
	BEGIN
	
	BEGIN TRAN INSERT_CALENDARS
	
	--SELECT *
	--INTO #Calendars
	--FROM ciberseguros.dbo.

	COMMIT TRAN INSERT_CALENDARS	
	PRINT 'CALENDARS IMPORTADOS'
	END



	-- Banks
	BEGIN

	BEGIN TRAN INSERT_BANKS

	SELECT b.CODIGO, NEWID() Id
	INTO #Banks
	FROM ciberseguros.dbo.BANCOS b
	WHERE b.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.Banks (Id, Name, Number)
	SELECT b.Id, ba.NOMBRE Name, ba.NUMERO Number
	FROM #Banks b
	INNER JOIN ciberseguros.dbo.BANCOS ba
		ON b.CODIGO = ba.CODIGO

	COMMIT TRAN INSERT_BANKS
	PRINT 'BANCOS IMPORTADOS'
	END


	---- Marital Statuses
	--BEGIN
	--BEGIN TRAN INSERT_ESTADO_CIVIL

	--SELECT EC.CODIGO, NEWID() Id
	--INTO #MaritalStatuses
	--FROM CiberSeguros.dbo.ESTADO_CIVIL EC
	--WHERE EC.Nombre IS NOT NULL

	--INSERT INTO Seggu.dbo.MaritalStatuses (Id, Name)
	--SELECT TMS.Id, EC.Nombre
	--FROM #MaritalStatuses TMS
	--INNER JOIN CiberSeguros.dbo.ESTADO_CIVIL EC
	--	ON TMS.CODIGO = EC.CODIGO

	--COMMIT TRAN INSERT_ESTADO_CIVIL
	--PRINT 'ESTADOS CIVILES IMPORTADOS'
	--END



	---- Document Types
	--BEGIN
	--BEGIN TRAN INSERT_DOCUMENT_TYPE

	--SELECT TD.Codigo, NEWID() Id
	--INTO #DocumentType
	--FROM CiberSeguros.dbo.TIPODOCU TD

	--INSERT INTO Seggu.dbo.DocumentTypes (Id, Name)
	--SELECT DT.Id, TD.NOMBRE
	--FROM #DocumentType DT
	--INNER JOIN CiberSeguros.dbo.TIPODOCU TD
	--	ON TD.Codigo = DT.Codigo

	--COMMIT TRAN INSERT_DOCUMENT_TYPE
	--PRINT 'TIPOS DOCUMENTOS'
	--END



	---- Ivas
	--BEGIN
	--BEGIN TRAN INSERT_IVA

	--SELECT I.CODIGO, NEWID() Id
	--INTO #Ivas
	--FROM CiberSeguros.dbo.IVA I
	--WHERE I.NOMBRE IS NOT NULL

	--INSERT INTO Seggu.dbo.IVAs (Id, Name)
	--SELECT TI.Id, I.NOMBRE
	--FROM #Ivas TI
	--INNER JOIN CiberSeguros.dbo.IVA I
	--	ON TI.CODIGO = I.CODIGO

	--COMMIT TRAN INSERT_IVA
	--PRINT 'IVAS IMPORTADOS'
	--END




	-- Provincias - Districts - Localities
	BEGIN

	BEGIN TRAN INSERT_PROVINCES

	SELECT P.CODIGO, NEWID() Id
	INTO #Provinces
	FROM CiberSeguros.dbo.PROVINC P
	WHERE P.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.Provinces (Id, Name)
	SELECT PR.Id, P.NOMBRE
	FROM CiberSeguros.dbo.PROVINC P
	INNER JOIN #Provinces PR ON PR.CODIGO = P.CODIGO
	
	SELECT DISTINCT A.LOCALIDAD Name, A.COD_PROVINCIA
	INTO #ProvinceLocalities
	FROM CiberSeguros.dbo.ASEGURADOS A
	WHERE A.LOCALIDAD IS NOT NULL AND A.LOCALIDAD <> ''

	SELECT NEWID() Id, 'Sin Distrito' Name, P.CODIGO COD_PROV, P.Id ProvinceId
	INTO #Districts
	FROM #Provinces P
	
	INSERT INTO Seggu.dbo.Districts (Id, Name, ProvinceId)
	SELECT D.Id, D.Name, D.ProvinceId
	FROM #Districts D

	SELECT NEWID() Id, PL.Name, D.Id DistrictId
	INTO #Localities
	FROM #ProvinceLocalities PL
	INNER JOIN #Districts D ON D.COD_PROV = PL.COD_PROVINCIA

	INSERT INTO Seggu.dbo.Localities (DistrictId, Id, Name)
	SELECT L.DistrictId, L.Id, L.Name
	FROM #Localities L

	COMMIT TRAN INSERT_PROVINCES
	PRINT 'PROVINCIAS, LOCALIDADES Y DEPARTAMENTOS IMPORTADOS'
	END



	-- Cheques
	BEGIN
	BEGIN TRAN INSERT_CHEQUES

	SELECT c.CODIGO, NEWID() Id
	INTO #Cheques
	FROM ciberseguros.dbo.CHEQUES c

	INSERT INTO Seggu.dbo.Cheques (Id, Number, Date, BankId, Value)
	SELECT c.Id, ch.NRO, ch.A_FECHA, b.Id, ch.IMPORTE
	FROM #Cheques c
	INNER JOIN ciberseguros.dbo.CHEQUES ch ON c.CODIGO = ch.CODIGO
	INNER JOIN #Banks b ON b.CODIGO = ch.COD_BANCO

	COMMIT TRAN INSERT_CHEQUES
	PRINT'CHEQUES IMPORTADOS'
	END



	-- Marital Statuses
	BEGIN
	BEGIN TRAN INSERT_ESTADO_CIVIL

	SELECT EC.CODIGO, NEWID() Id
	INTO #MaritalStatuses
	FROM CiberSeguros.dbo.ESTADO_CIVIL EC
	WHERE EC.Nombre IS NOT NULL

	INSERT INTO Seggu.dbo.MaritalStatuses (Id, Name)
	SELECT TMS.Id, EC.Nombre
	FROM #MaritalStatuses TMS
	INNER JOIN CiberSeguros.dbo.ESTADO_CIVIL EC
		ON TMS.CODIGO = EC.CODIGO

	COMMIT TRAN INSERT_ESTADO_CIVIL
	PRINT 'ESTADOS CIVILES IMPORTADOS'
	END



	-- Document Types
	BEGIN
	BEGIN TRAN INSERT_DOCUMENT_TYPE

	SELECT TD.Codigo, NEWID() Id
	INTO #DocumentType
	FROM CiberSeguros.dbo.TIPODOCU TD

	INSERT INTO Seggu.dbo.DocumentTypes (Id, Name)
	SELECT DT.Id, TD.NOMBRE
	FROM #DocumentType DT
	INNER JOIN CiberSeguros.dbo.TIPODOCU TD
		ON TD.Codigo = DT.Codigo

	COMMIT TRAN INSERT_DOCUMENT_TYPE
	PRINT 'TIPOS DOCUMENTOS'
	END



	-- Ivas
	BEGIN
	BEGIN TRAN INSERT_IVA

	SELECT I.CODIGO, NEWID() Id
	INTO #Ivas
	FROM CiberSeguros.dbo.IVA I
	WHERE I.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.IVAs (Id, Name)
	SELECT TI.Id, I.NOMBRE
	FROM #Ivas TI
	INNER JOIN CiberSeguros.dbo.IVA I
		ON TI.CODIGO = I.CODIGO

	COMMIT TRAN INSERT_IVA
	PRINT 'IVAS IMPORTADOS'
	END



	-- Producers
	BEGIN
	BEGIN TRAN INSERT_PRODUCTOR
	
	SELECT PC.CODIGO, NEWID() Id
	INTO #Productores
	FROM CiberSeguros.dbo.PRODCOBR PC
	
	INSERT INTO Seggu.dbo.Producers (Id, Name, RegistrationNumber, IsCollector, ProducerId )
	SELECT P.Id, ISNULL(PC.NOMBRE, 'Sin Productor'), ISNULL(PC.NRO_MATR, ''), ISNULL (PC.COBRANZA, 1), P.Id
	FROM CiberSeguros.dbo.PRODCOBR PC
	RIGHT JOIN #Productores P
		ON ISNULL(p.CODIGO, -1) = ISNULL(PC.CODIGO, -1)

	COMMIT TRAN INSERT_PRODUCTOR
	PRINT 'PRODUCTORES IMPORTADOS'
	END



	-- Risk Types
	BEGIN
	BEGIN TRAN INSERT_RISKTYPES
	
	SELECT tr.codigo, NEWID() Id
	INTO #RiskTypes
	FROM ciberseguros.dbo.TIPO_RIESGO tr

	INSERT INTO Seggu.dbo.RiskTypes (Id, Name)
	SELECT rt.Id, tr.nombre
	FROM #RiskTypes rt
	INNER JOIN ciberseguros.dbo.TIPO_RIESGO tr ON tr.codigo = rt.codigo

	COMMIT TRAN INSERT_RISKTYPES
	PRINT 'TIPOS DE RIESGOS IMPORTADOS'
	END



	-- Risks
	BEGIN
	BEGIN TRAN INSERT_RISKS
	
	SELECT r.codigo, NEWID() Id
	INTO #Risks
	FROM ciberseguros.dbo.RIESGO r

	INSERT INTO Seggu.dbo.Risks (Id, Description, RiskTypeId)
	SELECT r.Id, ri.DESCRIP, rt.Id
	FROM #Risks r
	INNER JOIN ciberseguros.dbo.RIESGO ri ON ri.codigo = r.codigo
	INNER JOIN #RiskTypes rt ON rt.codigo = ri.cod_tiporiesgo

	COMMIT TRAN INSERT_RISKS
	PRINT 'RIESGOS IMPORTADOS'
	END



	-- Collectors
	BEGIN
	BEGIN TRAN INSERT_COBRADOR

	--SELECT PC.CODIGO, NEWID() Id
	--INTO #Collectors
	--FROM CiberSeguros.dbo.PRODCOBR PC
	--WHERE PC.COBRANZA = 1

	--INSERT INTO Seggu.dbo.Collectors (Id, Name, ProducerId)
	--SELECT C.Id, PC.NOMBRE, p.Id
	--FROM #Collectors C
	--INNER JOIN CiberSeguros.dbo.PRODCOBR PC
	--	ON PC.CODIGO = C.CODIGO
	--LEFT JOIN #Productores p
	--	ON ISNULL(p.CODIGO, -1) = ISNULL(PC.COD_GRUPO, -1)

	COMMIT TRAN INSERT_COBRADOR
	PRINT 'COBRADORES IMPORTADOS'
	END



	-- Periods
	BEGIN
	BEGIN TRAN INSERT_PERIODS
	
	SELECT p.CODIGO, NEWID() Id
	INTO #Periods
	FROM ciberseguros.dbo.PERIODO p

	INSERT INTO Seggu.dbo.Periods (Id, Name)
	SELECT p.Id, pe.NOMBRE
	FROM #Periods p
	INNER JOIN ciberseguros.dbo.PERIODO pe ON pe.CODIGO = p.CODIGO

	COMMIT TRAN INSERT_PERIODS
	PRINT ' PERIODOS IMPORTADOS'
	END



	-- Companias
	BEGIN
	BEGIN TRAN INSERT_COMPANIAS

	SELECT C.CODIGO, NEWID() Id
	INTO #Companies
	FROM CiberSeguros.dbo.COMPAÑIAS C
	WHERE C.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.Companies (Id, Name, Notes, Phone, Active, EMail, Cuit)
	SELECT CO.Id, C.NOMBRE, '', C.TELEFONO1, C.ACTIVA, C.MAILEJ, ISNULL(C.CUIT, '')
	FROM CiberSeguros.dbo.COMPAÑIAS C
	INNER JOIN #Companies CO
		ON CO.CODIGO = C.CODIGO

	COMMIT TRAN INSERT_COMPANIAS	
	PRINT 'COMPANIAS IMPORTADAS'
	END



	-- Clients
	BEGIN
	BEGIN TRAN INSERT_ASEGURADOS

	SELECT A.CODIGO, NEWID() Id
	INTO #Clients
	FROM CiberSeguros.dbo.ASEGURADOS A

	DECLARE @SOLTERO_ID UNIQUEIDENTIFIER = (
		SELECT TOP 1 Id
		FROM Seggu.dbo.MaritalStatuses m
		WHERE m.Name = 'SOLTERO'
	)

	DECLARE @DNI_ID UNIQUEIDENTIFIER = (
		SELECT TOP 1 Id
		FROM Seggu.dbo.DocumentTypes dt
		WHERE dt.Name = 'DNI'
	)

	DECLARE @IVA_ID UNIQUEIDENTIFIER = (
		SELECT TOP 1 Id
		FROM Seggu.dbo.IVAs i
		WHERE i.Name = 'CONSUMIDOR FINAL'
	)

	INSERT INTO Seggu.dbo.Clients (
		BankingCode, 
		BirthDate, 
		CellPhone, 
		CollectionTimeRange, 
		Cuit, 
		Document, 
		DocumentTypeId, 
		FirstName,
		Id,
		IngresosBrutos,
		IsSmoker,
		IVA_Id,
		LastName,
		Mail,
		MaritalStatusId,
		Notes,
		Sex
	)
	SELECT
		A.CBU,
		A.FECHA_NAC,
		A.CELULAR,
		A.HORA_COBR,
		A.NRO_CUIT,
		isnull( A.DOCUMENTO,'sin'),
		ISNULL(DT.Id, @DNI_ID),
		A.NOM,
		C.Id,
		A.INGR_BRUT,
		A.FUMADOR,
		ISNULL(I.Id, @IVA_ID),
		A.APELLIDO,
		A.EMAIL,
		ISNULL(MS.Id, @SOLTERO_ID),
		A.NOTAS,
		'1'
	FROM CiberSeguros.dbo.ASEGURADOS A
	INNER JOIN #Clients C
		ON C.CODIGO = A.CODIGO
	LEFT JOIN #DocumentType DT
		ON DT.Codigo = A.TIPO_DOC
	LEFT JOIN #Ivas I
		ON I.CODIGO = A.IVA
	LEFT JOIN #MaritalStatuses MS
		ON MS.CODIGO = A.COD_ESTADO

	COMMIT TRAN INSERT_ASEGURADOS
	PRINT 'ASEGURADOS IMPORTADOS'	
	END



	-- Brands
	BEGIN
	BEGIN TRAN INSERT_BRANDS

	SELECT M.CODIGO, NEWID() Id
	INTO #Brands
	FROM CiberSeguros.dbo.MARCAS M
	WHERE M.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.Brands (Id, Name)
	SELECT B.Id, M.NOMBRE 
	FROM CiberSeguros.dbo.MARCAS M
	INNER JOIN #Brands B ON B.CODIGO = M.CODIGO

	COMMIT TRAN INSERT_BRANDS
	PRINT 'MARCAS IMPORTADAS'
	END
	
	
	-- Carrocerías	
	BEGIN
	BEGIN TRAN INSERT_bodyworks
	
	SELECT C.CODIGO, NEWID() Id
	INTO #Bodyworks
	FROM CiberSeguros.dbo.CARROCER C
	WHERE C.DESCRIPCION IS NOT NULL

	INSERT INTO Seggu.dbo.Bodyworks (Id, Name)
	SELECT B.Id, C.DESCRIPCION 
	FROM CiberSeguros.dbo.CARROCER C
	INNER JOIN #Bodyworks B ON B.CODIGO = C.CODIGO
	
	COMMIT TRAN INSERT_bodyworks
	PRINT 'CARROCERÍAS IMPORTADAS'
	END
	


	-- ProducerCodes
	BEGIN
	BEGIN TRAN INSERT_PRODUCERCODES

	INSERT INTO Seggu.dbo.ProducerCodes (ProducerId, CompanyId, Code)
	SELECT P.Id, C.Id, ''
	FROM CiberSeguros.dbo.CIA_PRODUCTOR CP
	INNER JOIN #Productores P
		ON P.CODIGO = CP.COD_PROD
	INNER JOIN #Companies C
		ON C.CODIGO = CP.COD_CIA

	COMMIT TRAN INSERT_PRODUCERCODES
	PRINT'CODIGOS DE PRODUCTORES IMPORTADOS'
	END



	-- RiskCompany
	BEGIN
	BEGIN TRAN INSERT_RISKCOMPANY

	INSERT INTO Seggu.dbo.CompanyRisk (Companys_Id, Risks_Id)
	SELECT c.Id, r.Id
	FROM ciberseguros.dbo.CIA_RIESGO cr
	INNER JOIN #Companies c ON c.CODIGO = cr.COD_CIA
	INNER JOIN #Risks r ON r.codigo = cr.COD_RIES
	
	COMMIT TRAN INSERT_RISKCOMPANY
	PRINT 'RIESGO POR COMPAÑIA IMPORTADOS'
	END



	-- CreditCards
	BEGIN
	BEGIN TRAN INSERT_CreditCards

	SELECT tc.CODIGO, NEWID() Id
	INTO #CreditCards
	FROM ciberseguros.dbo.TARJETAS tc

	INSERT INTO Seggu.dbo.CreditCards (Id, Name)
	SELECT cc.Id, t.NOMBRE
	FROM #CreditCards cc
	INNER JOIN ciberseguros.dbo.TARJETAS t ON t.CODIGO = cc.CODIGO

	COMMIT TRAN INSERT_CreditCards
	PRINT 'TARJETAS IMPORTADAS'
	END



	-- CompanyCreditCard
	BEGIN
	BEGIN TRAN INSERT_COMPANYCREDITCARD

	INSERT INTO Seggu.dbo.CreditCardCompany (Companies_Id, CreditCards_Id)
	SELECT c.Id, cc.Id
	FROM ciberseguros.dbo.CIA_TARJCRED ctc
	INNER JOIN #Companies c ON c.CODIGO = ctc.COD_CIA
	INNER JOIN #CreditCards cc ON cc.codigo = ctc.COD_TARJ

	COMMIT TRAN INSERT_COMPANYCREDITCARD
	PRINT 'TARJETAS POR COMPAÑIAS'
	END



	-- ClientCreditCards
	BEGIN 
	BEGIN TRAN INSERT_ClientCreditCards
	
	SELECT tc.CODIGO, NEWID() Id
	INTO #ClientCreditCards
	FROM ciberseguros.dbo.TARJ_CRE tc

	INSERT INTO Seggu.dbo.ClientCreditCards (Id, BankId, ClientId, CreditCardId, ExpirationDate, Number)
	SELECT ccc.Id, b.Id, c.Id, cc.Id, tc.VENCIM, tc.NUMERO
	FROM ciberseguros.dbo.TARJ_CRE tc
	INNER JOIN #ClientCreditCards ccc ON ccc.CODIGO = tc.CODIGO
	INNER JOIN #Banks b ON b.CODIGO = tc.COD_BANCO
	INNER JOIN #Clients c ON c.CODIGO = tc.COD_ASEG
	INNER JOIN #CreditCards cc ON cc.codigo = tc.COD_TARJ

	COMMIT TRAN INSERT_ClientCreditCards
	PRINT 'TARJETAS POR CLIENTES'
	END



	-- Addresses
	BEGIN
	BEGIN TRAN INSERT_DIRECCIONES

	--INSERT INTO Seggu.dbo.Addresses (
	--Appartment
	--, ClientId
	--, Floor
	--, Id
	--, LocalityId
	--, Number
	--, Phone
	--, PostalCode
	--, Street
	--, AddressType
	--)
	--SELECT 
	--T.Appartment
	--, T.ClientId
	--, T.Floor
	--, T.Id
	--, T.LocalityId
	--, T.Number
	--, T.Phone
	--, T.PostalCode
	--, T.Street
	--, T.AddressType
	--FROM (
	--	SELECT A.DEPTO Appartment, C.Id ClientId, A.PISO Floor, NEWID() Id, L.Id LocalityId, A.NUMERO Number, A.TELEFONO1 Phone, A.COD_POST PostalCode, A.CALLE Street, 0 AddressType
	--	FROM CiberSeguros.dbo.ASEGURADOS A
	--	INNER JOIN #Clients C
	--		ON C.CODIGO = A.CODIGO
	--	INNER JOIN Seggu.dbo.Localities L
	--		ON L.Name = A.LOCALIDAD
	--	UNION ALL
	--	SELECT '' Appartment, C.Id ClientId, '' Floor, NEWID() Id, L.Id LocalityId, '' Number, '' Phone, '' PostalCode, DIR_COBR Street, 1 AddressType
	--	FROM CiberSeguros.dbo.ASEGURADOS A
	--	INNER JOIN #Clients C
	--		ON C.CODIGO = A.CODIGO
	--	INNER JOIN Seggu.dbo.Localities L
	--		ON L.Name = A.LOC_COBR
	--	UNION ALL
	--	SELECT '' Appartment, C.Id ClientId, '' Floor, NEWID() Id, NULL LocalityId, '' Number, '' Phone, '' PostalCode, '' Street, 2 AddressType
	--	FROM CiberSeguros.dbo.ASEGURADOS A
	--	INNER JOIN #Clients C
	--		ON C.CODIGO = A.CODIGO
	--	INNER JOIN Seggu.dbo.Localities L
	--		ON L.Name = A.LOCALIDAD
	--) T

	COMMIT TRAN INSERT_DIRECCIONES
	PRINT 'DIRECCIONES IMPORTADAS'
	END



	-- Policies
	BEGIN
	BEGIN TRAN INSERT_Policies
	
	--SELECT p.CODIGO, NEWID() Id
	--INTO #Policies
	--FROM ciberseguros.dbo.POLIZAS p

	--INSERT INTO Seggu.dbo.Policies (
	--	Id, 
	--	AnnulationDate, 
	--	Annulled, 
	--	ClientId, 
	--	CollectorId, 
	--	CompanyId, 
	--	EmissionDate, 
	--	EndDate, 
	--	Number, 
	--	NumberVersion, 
	--	Observation, 
	--	PeriodId, 
	--	Premium, 
	--	Prima, 
	--	ReceptionDate, 
	--	Removed, 
	--	Renovated, 
	--	RequestDate, 
	--	RiskId, 
	--	StartDate, 
	--	Value
	--)
	--SELECT
	--	p.Id,
	--	pol.FECHANULPO,
	--	pol.ANULADA,
	--	c.Id, 
	--	pro.Id, 
	--	com.Id, 
	--	pol.FECH_EMIS, 
	--	pol.FECH_FIN, 
	--	pol.NRO_POLIZa, 
	--	0, 
	--	notas.nota, 
	--	per.Id, 
	--	pol.PREMIO, 
	--	pol.PRIMA, 
	--	pol.FECH_RECEP, 
	--	pol.ELIMINADA, 
	--	pol.RENOVADA, 
	--	pol.FECH_SOLIC, 
	--	r.Id, 
	--	pol.FECH_INIC, 
	--	pol.SUMA_ASEG
	--FROM #Policies p
	--INNER JOIN ciberseguros.dbo.POLIZAS pol ON pol.CODIGO = p.CODIGO
	--INNER JOIN #Clients c ON c.CODIGO = pol.COD_ASEG
	----INNER JOIN #Collectors col ON col.CODIGO = pol.COD_COBR
	--INNER JOIN #Companies com ON com.CODIGO = pol.COD_CIA
	--INNER JOIN #Periods per ON per.CODIGO = pol.COD_PERIODO
	--INNER JOIN #Risks r ON r.codigo = pol.COD_RIESGO
	--INNER JOIN (
	--	SELECT 
	--		pold.cod_poliza, 
	--		STUFF(
	--			(
	--				SELECT pd.[linea] + '\n'
	--				FROM ciberseguros.dbo.POL_DETALLE pd
	--				WHERE (pd.cod_poliza = pold.cod_poliza) 
	--				FOR XML PATH(''),TYPE
	--			).value('(./text())[1]','VARCHAR(MAX)'),
	--			1,
	--			2,
	--			''
	--		) AS nota
	--	FROM ciberseguros.dbo.POL_DETALLE pold
	--) notas ON p.CODIGO = notas.cod_poliza

	COMMIT TRAN INSERT_Policies
	PRINT 'POLIZAS IMPORTADAS'
	END



	-- CarModels
	BEGIN
	BEGIN TRAN INSERT_MODELOS

	SELECT M.CODIGO, NEWID() Id
	INTO #CarModels
	FROM CiberSeguros.dbo.MODELOS M
	WHERE M.NOMBRE IS NOT NULL

	INSERT INTO Seggu.dbo.CarModels (Id, Name, BrandId)
	SELECT CM.Id, M.NOMBRE Name, B.Id
	FROM CiberSeguros.dbo.MODELOS M
	INNER JOIN #CarModels CM
		ON CM.CODIGO = M.CODIGO
	LEFT JOIN #Brands B
		ON B.CODIGO = M.COD_MARCA
	WHERE M.NOMBRE IS NOT NULL
	
	COMMIT TRAN INSERT_MODELOS
	PRINT 'MODELOS IMPORTADOS'
	END
	


	-- CasualtyTypes
	BEGIN

	BEGIN TRAN INSERT_CasualtyTypes

	SELECT ts.CODIGO, NEWID() Id
	INTO #CasualtyTypes
	FROM ciberseguros.dbo.TIPO_SINIESTRO ts
	WHERE ts.NOMBRE IS NOT NULL AND ts.NOMBRE <> ''

	INSERT INTO Seggu.dbo.CasualtyTypes (Id, Name)
	SELECT ct.Id, ts.NOMBRE
	FROM #CasualtyTypes ct
	INNER JOIN ciberseguros.dbo.TIPO_SINIESTRO ts ON ts.CODIGO = ct.CODIGO

	COMMIT TRAN INSERT_CasualtyTypes

	END



	-- EndorseTypes
	BEGIN
	BEGIN TRAN INSERT_ENDORSETYPES
	
	SELECT TE.CODIGO, NEWID() Id
	INTO #EndorseTypes
	FROM CiberSeguros.dbo.TIPO_END TE
	
	INSERT INTO Seggu.dbo.EndorseTypes
	SELECT ET.Id, TE.nombre
	FROM #EndorseTypes ET
	INNER JOIN CiberSeguros.dbo.TIPO_END TE
		ON TE.CODIGO = ET.CODIGO
		
	COMMIT TRAN INSERT_ENDORSETYPES
	PRINT 'TIPOS DE ENDOSOS IMPORTADOS'	
	END



	-- Fees
	BEGIN

	BEGIN TRAN INSERT_Fees

	--SELECT *
	--INTO #Fees
	--FROM ciberseguros.dbo.

	COMMIT TRAN INSERT_Fees

	END



	-- AccesoryTypes
	BEGIN
	BEGIN TRAN INSERT_ACCESSORYTYPES

	SELECT ta.CODIGO, NEWID() Id
	INTO #AccessoryTypes
	FROM ciberseguros.dbo.TIPO_ACCESORIO ta

	INSERT INTO Seggu.dbo.AccessoryTypes(Id, Name)
	SELECT at.Id, ta.NOMBRE Name
	FROM #AccessoryTypes at
	INNER JOIN ciberseguros.dbo.TIPO_ACCESORIO ta
		ON at.CODIGO = ta.CODIGO

	COMMIT TRAN INSERT_ACCESSORYTYPES
	PRINT 'TIPOS ACCESORIOS IMPORTADOS'
	END



	-- Accessories
	BEGIN

	BEGIN TRAN INSERT_ACCESSORIES
	
	--SELECT a.CODIGO, NEWID() Id
	--INTO #Accesories
	--FROM ciberseguros.dbo.ACCESORI a
		
	--INSERT INTO Seggu.DBO.Accessories (Id, AccessoryTypeId, CarPolicyId, ExpirationDate, Stamp)
	--SELECT 
	--a.Id
	
	--SELECT TOP 1000 [CODIGO]
	--	,[Cod_auto]
	--	,[Cod_endoso]
	--	,[NOMBRE]
	--	,[VALOR]
	--	,[COD_TIPOACCESORIO]
	--	,[OBLEA]
	--	,[VENCIMIENTO]
	--FROM [CiberSeguros].[dbo].[ACCESORI]
	
	--FROM #Accesories a
	--INNER JOIN CiberSeguros.dbo.ACCESORI b
	--	ON a.CODIGO = b.CODIGO
	--INNER JOIN #AccessoryTypes at
	--	ON at.
	
	COMMIT TRAN INSERT_ACCESORIES
	
	PRINT 'ACCESORIOS IMPORTADOS'
	
	END



	--COMMIT TRAN INSERT_DATA

	PRINT 'the end'

END TRY

BEGIN CATCH

	IF @@TRANCOUNT > 0 ROLLBACK TRAN

	PRINT 'ERROR: LINE(' + CAST(ERROR_LINE() AS NVARCHAR(100)) + '), MESSAGE("' + ERROR_MESSAGE() + '")'

END CATCH
